<?php

namespace app\controllers;

use Yii;
use yii\db\Query;
use yii\web\Response;

class DbController extends BaseController
{
    public function actionIndex()
    {
        //\Yii::$app->response->format = Response::FORMAT_JSON;
        //$data = Yii::$app->db->createCommand('select * from lamp_user')->queryAll();
        //return $data;
        // 打开数据库连接
        Yii::$app->db->open();

        // 关闭数据库连接
        //Yii::$app->db->close();
        if (Yii::$app->db->getIsActive()) {
            echo "数据库连接已建立且处于活动状态";
        } else {
            echo "数据库连接未建立或已关闭";
        }
    }

    public function actionCheckTs()
    {
        $db = Yii::$app->db;
        $db->beginTransaction();
        $transaction = $db->getTransaction(); // 获取当前事务对象

        if ($transaction !== null && $transaction->getIsActive()) {
            echo '当前处于事务中';
        } else {
            echo '当前不在事务中';
        }
    }

    /**
     * 参数绑定
     * 字符串类型参数 参数值会包含单引号
     * int类型       不会包含
     */
    public function actionBindParam()
    {
        \Yii::$app->response->format = Response::FORMAT_JSON;
        $data = ['utime' => date('Y-m-d H:i:s')];
        $command = Yii::$app->db->createCommand()->update('lamp_user', $data, 'status = :status', [':status' => 1]);
        $rt[] = $command->getRawSql();  // 输出status=1 不带引号
        $command = Yii::$app->db->createCommand()->update('lamp_user', $data, 'status = :status', [':status' => '1']);
        $rt[] = $command->getRawSql();  // 输出status='1' 带引号

        $command = Yii::$app->db->createCommand('select * from lamp_user where status = :status', [':status' => '1']);
        $rt[] = $command->getRawSql();  // 输出status='1' 带引号

        //createCommand() 普通参数绑定只支持int和string  错误的IN使用方法
        $command = Yii::$app->db->createCommand('select * from lamp_user where status IN(:status)', [':status' => [3,2,1]]);
        $rt[] = $command->getRawSql();  // 输出 status IN(3)


        return $rt;
    }

    public function actionQuery()
    {
        $query = new Query();
        $query->from('lamp_user')->where(['status' => 1])->orderBy('utime DESC')->limit(10);
        $data['sql'] = $query->createCommand()->getsql();
        return $data;
    }

}